# -*- coding: utf-8 -*-
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
from comment.logger_handle import LoggerHandler

logger = LoggerHandler("ExcelHandel")


class ExcelHandel(object):

    # 存储测试结果数据
    dict_result = []

    def __init__(self, file_path):
        # 初始化excel文件
        self.file_path = file_path

    def open_sheet(self, name) -> Worksheet:
        """ 打开表单，在函数或方法后 加 -> 类型：表示此函数返回值是 这个类型
        -> 的功能是 函数注释
        """
        wb = load_workbook(self.file_path)
        sheet = wb[name]
        wb.close()
        return sheet

    def header(self, sheet_name):
        """获取表头封装到列表中"""
        sheet = self.open_sheet(sheet_name)
        headers = []
        for header in sheet[1]:
            headers.append(header.value)
        return headers

    dict_caseId = {}

    def read(self, sheet_name):
        logger.debug("读取%s表的用例数据" % sheet_name)
        sheet = self.open_sheet(sheet_name)
        rows = list(sheet.rows)     # 获取每行的数据，并放到list中
        data = []
        # 遍历每行的数据（除去首行）
        row_num = 2
        for row in rows[1:]:
            cell_data = []
            data_dict = {}
            self.dict_caseId[row[0].value] = row_num
            row_num = row_num+1
            for cell in row:
                cell_data.append(cell.value)
                # 用zip方法将 两个列表合并成字段的格式
                data_dict = dict(zip(self.header(sheet_name), cell_data))
            # 根据是否执行，添加到data中
            if data_dict['execute'].lower() == 'yes':
                data.append(data_dict)
        return data

    def write(self, sheet_name, dict_data):
        logger.debug("回写%s表的用例数据" % sheet_name)
        """根据行数和列名 写入excel数据， 后续需要优化成批量写入后再关闭，提高效率"""
        wb = load_workbook(self.file_path)
        sheet = wb[sheet_name]

        # 获取首行标题
        headers = []
        for header in sheet[1]:
            headers.append(header.value)

        # 根据行列号写入数据
        for data in dict_data:
            row = ExcelHandel(self.file_path).dict_caseId.get(data['case_id'])
            a_column = headers.index("actual") + 1
            sheet.cell(row, a_column).value = str(data['actual'])
            r_column = headers.index("result") + 1
            sheet.cell(row, r_column).value = data['result']
        wb.save(self.file_path)
        wb.close()


# if __name__ == '__main__' :

#     file_path1 = "../case_excel/testcase.xlsx"
#
#     data = ExcelHandel(file_path1).read("Sheet1")
#     print(data)
    # ExcelHandel.write(file_path1, "Sheet1", 10, "name", "test_n8")

